package com.sync.platform.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Database utility for JDBC operations
 */
public class DatabaseUtil {

    private static final Logger logger = LoggerFactory.getLogger(DatabaseUtil.class);

    /**
     * Create a database connection
     *
     * @param url JDBC URL
     * @param driver JDBC driver class
     * @param username Database username
     * @param password Database password
     * @return Database connection
     * @throws ClassNotFoundException if driver class not found
     * @throws SQLException if error occurs during connection
     */
    public static Connection getConnection(String url, String driver, String username, String password)
            throws ClassNotFoundException, SQLException {
        try {
            Class.forName(driver);
            logger.debug("Loaded JDBC driver: {}", driver);
        } catch (ClassNotFoundException e) {
            logger.error("Failed to load JDBC driver: {}. Make sure the driver JAR is in the classpath.", driver);
            throw new ClassNotFoundException("JDBC driver not found: " + driver, e);
        }
        
        try {
            logger.debug("Attempting to connect to database with URL: {}, username: {}", url, username);
            Connection connection = DriverManager.getConnection(url, username, password);
            logger.debug("Successfully connected to database: {}", url);
            
            // Log database metadata for debugging
            DatabaseMetaData metaData = connection.getMetaData();
            logger.debug("Connected to: {} {}, driver: {} {}",
                    metaData.getDatabaseProductName(),
                    metaData.getDatabaseProductVersion(),
                    metaData.getDriverName(),
                    metaData.getDriverVersion());
                    
            return connection;
        } catch (SQLException e) {
            logger.error("Failed to connect to database URL: {}, error: {}", url, e.getMessage());
            throw e;
        }
    }

    /**
     * Test database connection
     *
     * @param url JDBC URL
     * @param driver JDBC driver class
     * @param username Database username
     * @param password Database password
     * @return true if connection successful, false otherwise
     */
    public static boolean testConnection(String url, String driver, String username, String password) {
        Connection conn = null;
        try {
            conn = getConnection(url, driver, username, password);
            return true;
        } catch (Exception e) {
            logger.error("Database connection test failed: {}", e.getMessage(), e);
            return false;
        } finally {
            closeConnection(conn);
        }
    }

    /**
     * Get database tables
     *
     * @param connection Database connection
     * @return List of table names
     * @throws SQLException if error occurs during query
     */
    public static List<String> getTables(Connection connection) throws SQLException {
        List<String> tables = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();
        
        // Get the current catalog (database name)
        String catalog = connection.getCatalog();
        
        // Get the database product name to handle database-specific behaviors
        String dbProduct = metaData.getDatabaseProductName().toLowerCase();
        logger.info("Getting tables from database product: {}, catalog: {}", dbProduct, catalog);
        
        // For MySQL and most databases, we can filter by catalog
        if (dbProduct.contains("mysql") || dbProduct.contains("mariadb")) {
            // MySQL uses catalog to filter by database
            logger.info("Using MySQL specific table retrieval for catalog: {}", catalog);
            ResultSet rs = metaData.getTables(catalog, null, "%", new String[]{"TABLE"});
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                tables.add(tableName);
                logger.debug("Found MySQL table: {}", tableName);
            }
            rs.close();
        } else if (dbProduct.contains("postgresql")) {
            // PostgreSQL uses schema instead of catalog, with 'public' as default schema
            logger.info("Using PostgreSQL specific table retrieval for schema: public");
            ResultSet rs = metaData.getTables(null, "public", "%", new String[]{"TABLE"});
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                tables.add(tableName);
                logger.debug("Found PostgreSQL table: {}", tableName);
            }
            rs.close();
        } else if (dbProduct.contains("oracle")) {
            // For Oracle, use the current user's schema
            String schema = connection.getMetaData().getUserName();
            logger.info("Using Oracle specific table retrieval for schema: {}", schema);
            ResultSet rs = metaData.getTables(null, schema.toUpperCase(), "%", new String[]{"TABLE"});
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                tables.add(tableName);
                logger.debug("Found Oracle table: {}", tableName);
            }
            rs.close();
        } else {
            // Default approach - try catalog first, then fallback to all tables
            logger.info("Using default table retrieval approach for catalog: {}", catalog);
            ResultSet rs = metaData.getTables(catalog, null, "%", new String[]{"TABLE"});
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                tables.add(tableName);
                logger.debug("Found table: {}", tableName);
            }
            rs.close();
        }
        
        logger.info("Retrieved {} tables from database", tables.size());
        return tables;
    }

    /**
     * Get table columns
     *
     * @param connection Database connection
     * @param tableName Table name
     * @return List of maps containing column name, type, and other metadata
     * @throws SQLException if error occurs during query
     */
    public static List<Map<String, String>> getColumns(Connection connection, String tableName) throws SQLException {
        List<Map<String, String>> columns = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getColumns(null, null, tableName, "%");

        while (rs.next()) {
            Map<String, String> column = new HashMap<>();
            column.put("name", rs.getString("COLUMN_NAME"));
            column.put("type", rs.getString("TYPE_NAME"));
            column.put("size", rs.getString("COLUMN_SIZE"));
            column.put("nullable", rs.getString("IS_NULLABLE"));

            columns.add(column);
        }

        rs.close();
        return columns;
    }

    /**
     * Execute a query
     *
     * @param connection Database connection
     * @param sql SQL query
     * @return List of maps containing query results (row -> column -> value)
     * @throws SQLException if error occurs during query
     */
    public static List<Map<String, Object>> executeQuery(Connection connection, String sql) throws SQLException {
        List<Map<String, Object>> results = new ArrayList<>();

        try (Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(sql)) {

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();

                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }

                results.add(row);
            }
        }

        return results;
    }

    /**
     * Execute update SQL (INSERT, UPDATE, DELETE)
     *
     * @param connection Database connection
     * @param sql SQL statement
     * @return Number of affected rows
     * @throws SQLException if error occurs during execution
     */
    public static int executeUpdate(Connection connection, String sql) throws SQLException {
        try (Statement stmt = connection.createStatement()) {
            return stmt.executeUpdate(sql);
        }
    }

    /**
     * Execute batch SQL statements
     *
     * @param connection Database connection
     * @param sqlList List of SQL statements
     * @return Array of affected row counts
     * @throws SQLException if error occurs during execution
     */
    public static int[] executeBatch(Connection connection, List<String> sqlList) throws SQLException {
        connection.setAutoCommit(false);

        try (Statement stmt = connection.createStatement()) {
            for (String sql : sqlList) {
                stmt.addBatch(sql);
            }

            int[] results = stmt.executeBatch();
            connection.commit();
            return results;
        } catch (SQLException e) {
            connection.rollback();
            throw e;
        } finally {
            connection.setAutoCommit(true);
        }
    }

    /**
     * Close database connection
     *
     * @param connection Database connection
     */
    public static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                logger.error("Error closing database connection: {}", e.getMessage(), e);
            }
        }
    }
}
